package cn.edu.zhku.citrusmonitor.person.expert.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.TbExpert;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;

public class ExpertDaoImpl implements ExpertDao {
	BeanProcessor bean=new GenerousBeanProcessor();
	RowProcessor processor=new BasicRowProcessor(bean);
	/**
	 * 通过专家名获取对象
	 */
	@Override
	public Object getUserByExpertName(String expertName) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from expert where expert_name=?";
		return qr.query(sql, new BeanHandler<TbExpert>(TbExpert.class,processor),expertName);
		
	}
	/**
	 * 通过专家名和密码获取对象
	 */
	@Override
	public TbExpert getUserByExpertNameAndPwd(String expertName, String password) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from expert where expert_name=? and password=?";
		
		return qr.query(sql, new BeanHandler<TbExpert>(TbExpert.class,processor),expertName,password);
	
	}
	/**
	 * 添加用户
	 * @throws SQLException 
	 */
	@Override
	public int addExpertByBean(TbExpert expert) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="insert into expert(expert_name,password,work_field,introduction,work_place,img_path,email) values(?,?,?,?,?,?,?)";
		return qr.update(sql,expert.getExpertName(),expert.getPassword(),
				expert.getWorkField(),expert.getIntroduction(),
				expert.getWorkPlace(),expert.getImgPath(),expert.getEmail());
	}
	/**
	 * 修改信息
	 */
	@Override
	public int modify(TbExpert expert) throws SQLException {
		// TODO Auto-generated method stub
		List<String> list=new ArrayList<String>();
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="update expert set expert_id=?";
		list.add(expert.getExpertId().toString());
		if(expert.getExpertName()!=null) {
			sql+=",expert_name=?";
			list.add(expert.getExpertName());
		}
		if(expert.getPassword()!=null) {
			sql+=",password=?";
			list.add(expert.getPassword());
		}
		if(expert.getWorkField()!=null) {
			sql+=",work_field=?";
			list.add(expert.getWorkField());
		}
		if(expert.getIntroduction()!=null) {
			sql+=",introduction=?";
			list.add(expert.getIntroduction());
		}
		if(expert.getWorkPlace()!=null) {
			sql+=",work_place=?";
			list.add(expert.getWorkPlace());
		}
		if(expert.getImgPath()!=null) {
			sql+=",img_path=?";
			list.add(expert.getImgPath());
		}
		if(expert.getEmail()!=null) {
			sql+=",email=?";
			list.add(expert.getEmail());
		}
		if(expert.getStatus()!=null) {
			sql+=",status=?";
			list.add(expert.getStatus().toString());
		}
		sql+=" where expert_id=?";
		list.add(expert.getExpertId().toString());
		return qr.update(sql,list.toArray());
	}
	/**
	 * 根据id删除
	 */
	@Override
	public int deleteExpertById(String id) throws SQLException {
		// TODO Auto-generated method stub
		String sql="delete from expert where expert_id=?";
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		return qr.update(sql,id);
		
	}
	/**
	 * 根据id获得用户
	 */
	@Override
	public TbExpert getExpertById(String id) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from expert where expert_id=?";
		return qr.query(sql,new BeanHandler<TbExpert>(TbExpert.class,processor),id);
	}
	/**
	 * 获得总记录数
	 */
	@Override
	public long getTotal() throws SQLException {
		// TODO Auto-generated method stub
		String sql="select count(*) from expert";
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		
		return (long) qr.query(sql, new ScalarHandler(1));
	}
	/**
	 * 获得对象集合
	 * @throws SQLException 
	 */
	@Override
	public List<TbExpert> getList(int currentPage, long pageSize) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from expert limit ?,?";
		return qr.query(sql, new BeanListHandler<TbExpert>(TbExpert.class,processor),(currentPage-1)*pageSize,pageSize);
		
	}
	/**
	 * 根据专家名查询
	 * @throws SQLException 
	 */
	@Override
	public TbExpert getExpertByName(String name) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from expert where expert_name=?";
		return qr.query(sql, new BeanHandler<TbExpert>(TbExpert.class,processor),name);
	
	}
	/**
	 * 根据id批量删除
	 * @throws SQLException 
	 */
	@Override
	public int[] removeAllById(String[] ids) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from expert where expert_id=?";
		String[][] values=new String[ids.length][];
		for(int i=0;i<ids.length;i++) {
			values[i]=new String[] {ids[i]};
		}
		
		return qr.batch(sql, values);
		
	}
	
}
